// 博客栏目模型
const { query } = require('../config/database');

class BlogColumn {
    // 创建栏目
    static async create(columnData) {
        const { name, slug, description, categories = [], sortOrder = 0 } = columnData;
        
        const result = await query(
            'INSERT INTO blog_columns (name, slug, description, sort_order) VALUES (?, ?, ?, ?)',
            [name, slug, description, sortOrder]
        );
        
        const columnId = result.insertId;
        
        // 保存栏目与分类的关系
        if (categories.length > 0) {
            await this.saveColumnCategories(columnId, categories);
        }
        
        return await this.findById(columnId);
    }
    
    // 根据ID查找栏目
    static async findById(id) {
        const [rows] = await query(
            'SELECT * FROM blog_columns WHERE id = ? AND deleted_at IS NULL',
            [id]
        );
        
        if (rows.length === 0) return null;
        
        const column = rows[0];
        column.categories = await this.getColumnCategories(id);
        
        return column;
    }
    
    // 获取所有栏目
    static async findAll(page = 1, limit = 100) {
        const offset = (page - 1) * limit;
        
        const [rows] = await query(
            'SELECT * FROM blog_columns WHERE deleted_at IS NULL ORDER BY sort_order, created_at DESC LIMIT ? OFFSET ?',
            [limit, offset]
        );
        
        const [countRows] = await query(
            'SELECT COUNT(*) as total FROM blog_columns WHERE deleted_at IS NULL'
        );
        
        // 为每个栏目加载分类信息
        const columns = await Promise.all(
            rows.map(async (column) => {
                column.categories = await this.getColumnCategories(column.id);
                return column;
            })
        );
        
        return {
            columns,
            total: countRows[0].total,
            totalPages: Math.ceil(countRows[0].total / limit)
        };
    }
    
    // 更新栏目
    static async update(id, columnData) {
        const { name, slug, description, categories, sortOrder } = columnData;
        
        const updateFields = [];
        const updateValues = [];
        
        if (name !== undefined) {
            updateFields.push('name = ?');
            updateValues.push(name);
        }
        
        if (slug !== undefined) {
            updateFields.push('slug = ?');
            updateValues.push(slug);
        }
        
        if (description !== undefined) {
            updateFields.push('description = ?');
            updateValues.push(description);
        }
        
        if (sortOrder !== undefined) {
            updateFields.push('sort_order = ?');
            updateValues.push(sortOrder);
        }
        
        if (updateFields.length > 0) {
            updateValues.push(id);
            
            await query(
                `UPDATE blog_columns SET ${updateFields.join(', ')}, updated_at = NOW() WHERE id = ?`,
                updateValues
            );
        }
        
        // 更新栏目分类关系
        if (categories !== undefined) {
            await this.updateColumnCategories(id, categories);
        }
        
        return await this.findById(id);
    }
    
    // 删除栏目（软删除）
    static async delete(id) {
        await query(
            'UPDATE blog_columns SET deleted_at = NOW() WHERE id = ?',
            [id]
        );
        
        // 同时删除栏目分类关系
        await query(
            'DELETE FROM column_categories WHERE column_id = ?',
            [id]
        );
    }
    
    // 获取栏目的分类列表
    static async getColumnCategories(columnId) {
        const [rows] = await query(`
            SELECT c.* 
            FROM blog_categories c
            INNER JOIN column_categories cc ON c.id = cc.category_id
            WHERE cc.column_id = ? AND c.deleted_at IS NULL
            ORDER BY c.sort_order, c.name
        `, [columnId]);
        
        return rows;
    }
    
    // 保存栏目分类关系
    static async saveColumnCategories(columnId, categoryIds) {
        // 先删除现有的关系
        await query('DELETE FROM column_categories WHERE column_id = ?', [columnId]);
        
        // 插入新的关系
        if (categoryIds.length > 0) {
            const values = categoryIds.map(categoryId => [columnId, categoryId]);
            await query(
                'INSERT INTO column_categories (column_id, category_id) VALUES ?',
                [values]
            );
        }
    }
    
    // 更新栏目分类关系
    static async updateColumnCategories(columnId, categoryIds) {
        await this.saveColumnCategories(columnId, categoryIds);
    }
    
    // 根据分类获取栏目
    static async findByCategory(categoryId) {
        const [rows] = await query(`
            SELECT c.* 
            FROM blog_columns c
            INNER JOIN column_categories cc ON c.id = cc.column_id
            WHERE cc.category_id = ? AND c.deleted_at IS NULL
            ORDER BY c.sort_order, c.name
        `, [categoryId]);
        
        return rows;
    }
    
    // 获取栏目统计信息
    static async getStats(columnId) {
        const [categoryCount] = await query(
            'SELECT COUNT(*) as category_count FROM column_categories WHERE column_id = ?',
            [columnId]
        );
        
        const [articleCount] = await query(`
            SELECT COUNT(*) as article_count 
            FROM articles a
            INNER JOIN blog_categories c ON a.category = c.id
            INNER JOIN column_categories cc ON c.id = cc.category_id
            WHERE cc.column_id = ? AND a.status = 'published' AND a.deleted_at IS NULL
        `, [columnId]);
        
        return {
            categoryCount: categoryCount[0].category_count,
            articleCount: articleCount[0].article_count
        };
    }
}

module.exports = BlogColumn;